# -*- coding: utf-8 -*-
'''
@author: olivia.dou
Created on: 2018/10/10 11:19
desc: 针对智讯产品用户管理的函数库
'''
import logging, time
from datetime import datetime
from util.db_util import execute_query
from pytz import timezone


def get_user_pay(username):
    try:
        results=execute_query("SELECT pay FROM user where username='%s';" % username)
        return results[0][0]
    except Exception as e:
        logging.exception("no result retrieved")

def get_user_count():
    try:
        results = execute_query("SELECT count(*) FROM user;")
        return results[0][0]
    except Exception as e:
        logging.exception("Retrieve user count failed!")
        return None


def get_role_count():
    try:
        results = execute_query("SELECT count(*) FROM user_role;")
        return results[0][0]
    except Exception as e:
        logging.exception("Retrieve role count failed!")
        return None


def get_authority_count():
    try:
        results = execute_query("SELECT count(*) FROM user_resource;")
        return results[0][0]
    except Exception as e:
        logging.exception("Retrieve authority count failed!")
        return None


def get_user_id(username):
    results = execute_query("SELECT id FROM user where username='%s';" % username)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None

def get_user_id_str(username):
    id = get_user_id(username)
    if id is not None:
        return str(id)
    else:
        return ''

def get_user_field(username, fieldname):
    results = execute_query("SELECT %s FROM user where username='%s';" % (fieldname, username))
    try:
        result = results[0][0]
        if isinstance(result, datetime):
            result = result.strftime('%Y-%m-%d %H:%M:%S')
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def get_user_rolename(username):
    results = execute_query(
        "SELECT name FROM user_role INNER JOIN user on user.role_id=user_role.id where username='%s';" % (username))
    try:
        return results[0][0]

    except Exception as e:
        logging.error("no result retrieved")
        return None


def get_user_resources(username):
    # role_id = execute_query("SELECT id FROM user_role INNER JOIN user on user.role_id=user_role.id where username='%s';"%(username))
    resource_id_liststr = execute_query(
        "SELECT a.resource_id FROM user_role a INNER JOIN user b on b.role_id=a.id where username='%s';" % (username))[
        0][0]
    if resource_id_liststr is not None and resource_id_liststr != '':
        resource_id_list = resource_id_liststr.split(',')
    else:
        resource_id_list = []
    resource_list = []
    for id in resource_id_list:
        if id == '':
            continue
        resource = execute_query("SELECT url FROM user_resource where id=%d order by url" % int(id))[0][0]
        resource_list.append(resource)
    if resource_list != []:
        return resource_list
    else:
        return None


def get_role_id(rolename):
    results = execute_query("SELECT id FROM user_role where name='%s';" % rolename)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def get_authority_id(authname):
    results = execute_query("SELECT id FROM user_resource where name='%s';" % authname)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def setpasswd(username, passwd):
    try:
        execute_query("UPDATE user set password='%s' where username='%s'" % (passwd, username))
        # logging.debug("update password succeed!")
        # return execute_query("select password from user where username='%s'"%(username))[0][0]
    except Exception as e:
        logging.exception("Error occured when updating password for user %s" % username)


def get_user_query_condition_id(username, condition_name):
    """

    :param username: 待查询的用户名
    :param condition_name: 待查询的条件名
    :return: 查询条件的id
    """
    userid = get_user_id(username)
    sql = "SELECT id FROM nlp_news_query_condition where user_id=%d and name='%s'" % (userid, condition_name)
    results = execute_query(sql)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


# 查询可用公告notice的id
def get_user_notice_id():
    results = execute_query("SELECT id FROM nlp_news_notice where disable_time > now() order by crt desc")
    if len(results) > 0:
        try:
            result = results[0][0]
            return result
        except Exception as e:
            logging.error("Retrieve user notice failed!")
            return None
    else:
        result = 0
        return result


# 查询用户添加关注关键词最新一条id
def get_user_latest_focus_keyword_id(username):
    try:
        results = execute_query(
            "SELECT id FROM nlp_news_user_keywords where user_id='%s' order by upt desc" % get_user_id(username))
        return results[0][0]
    except Exception as e:
        logging.exception("Retrieve focus keyword id failed!")
        return None


# 查询用户所在时区偏移量
def get_timezone(country):
    cst_tz = timezone(country)
    offset = datetime.now(cst_tz).utcoffset().total_seconds() / 3600
    return int(offset)
